package cn.edu.hpu.jie.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;

public class DBHelp<T> {
	private BasicDataSource bds;

	/*static {
		Properties properties = new Properties();
		try {
			properties.load(DBHelp.class.getClassLoader().getResourceAsStream("vote_db.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		String driver = properties.getProperty("driver");
		String url = properties.getProperty("url");
		String username = properties.getProperty("username");
		String password = properties.getProperty("password");
		
		bds.setDriverClassName(driver);
		bds.setUrl(url);
		bds.setUsername(username);
		bds.setPassword(password);
		
		bds.setInitialSize(5);
		bds.setMaxWait(5000);
		bds.setMaxActive(20);
		bds.setMaxIdle(10);
	}*/
	
	public DBHelp(BasicDataSource bds) {
		this.bds = bds;
	}
	
	public Connection getConnection() throws SQLException {
		return bds.getConnection();
	}
	
	/**
	 * 根据sql查询所有满足条件的记录
	 * @param sql 传入的预编译的sql语句
	 * @param rm 调用者实现的RomMapping接口的对象
	 * @param objs 可变参数，sql的条件
	 * @return 满足条件的记录
	 * @throws SQLException 抛出的异常
	 */
	public List<T> getAll(String sql, RowMapping<T> rm, Object...objs) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<T> list = new ArrayList<T>();
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for(int i = 0; i < objs.length; i++) {
				stat.setObject(i + 1, objs[i]);
			}
			
			rs = stat.executeQuery();
			System.out.println("SQL:" + sql);
			while(rs.next()) {
				list.add(rm.getMapping(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			list = null;
		} finally {
			close(conn, stat, rs);
		}
		
		return list;
	}
	
	/**
	 * 根据sql查询唯一的记录
	 * @param sql 传入的预编译的sql语句
	 * @param rm 调用者实现的RomMapping接口的对象
	 * @param objs 可变参数，sql的条件
	 * @return 满足条件的记录
	 * @throws SQLException 抛出的异常
	 */
	public T getObject(String sql, RowMapping<T> rm, Object...objs) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		T t = null;
		
		try {
			conn = bds.getConnection();
			stat = conn.prepareStatement(sql);
			for(int i = 0; i < objs.length; i++) {
				stat.setObject(i + 1, objs[i]);
			}
			
			rs = stat.executeQuery();
			System.out.println("SQL:" + sql);
			if(rs.next()) {
				t = rm.getMapping(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, stat, rs);
		}
		
		return t;
	}
	
	/**
	 * 根据sql更新数据（插入、修改、删除）
	 * @param sql 传入的预编译的sql语句
	 * @param objs 可变参数，sql的条件
	 * @return lines 受影响的行数
	 */
	public int updateData(String sql, Object...objs) {
		Connection conn = null;
		PreparedStatement stat = null;
		int lines = 0;
		
		try {
			conn = bds.getConnection();
			stat = conn.prepareStatement(sql);
			for(int i = 0; i < objs.length; i++) {
				stat.setObject(i + 1, objs[i]);
			}
			
			lines = stat.executeUpdate();
			System.out.println("SQL:" + sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, stat);
		}
		
		return lines;
	}
	
	/**
	 * 获取插入数据后自动生成的主键
	 * @param sql
	 * @param objs
	 * @return
	 * @throws SQLException
	 */
	public int getInsertPrimaryKey(String sql, Object...objs) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		int primaryKey = 0;
		
		try {
			conn = bds.getConnection();
			stat = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			for(int i = 0; i < objs.length; i++) {
				stat.setObject(i + 1, objs[i]);
			}
			
			stat.executeUpdate();
			rs = stat.getGeneratedKeys();
			System.out.println("SQL:" + sql);
			
			if(rs.next()) {
				primaryKey = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, stat, rs);
		}
		
		return primaryKey;
	}
	
	public List<String> getNoAll(String sql, Object...objs) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<String> list = new ArrayList<String>();
		
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			for(int i = 0; i < objs.length; i++) {
				stat.setObject(i + 1, objs[i]);
			}
			
			rs = stat.executeQuery();
			
			while(rs.next()) {
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, stat, rs);
		}
		return list;
	}
	
	/**
	 * 关闭连接
	 * @param conn
	 * @param stat
	 */
	public void close(Connection conn, PreparedStatement stat) {
		close(conn, stat, null);
	}
	
	/**
	 * 关闭连接
	 * @param conn
	 * @param stat
	 * @param rs
	 */
	public void close(Connection conn, PreparedStatement stat, ResultSet rs) {
		try {
			try {
				if (rs != null) {
					rs.close();
				}
			} finally {
				try {
					if(stat != null) {
						stat.close();
					}
				} finally {
					if(conn != null) {
						conn.close();
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
